﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using MVCAddressBook.Models.Entities;
using MVCAddressBook.Models.Interfaces;
using System.Data.SqlClient;
using System.Data;

namespace MVCAddressBook.Models.Models
{
    public class UserService:IUserService
    {
        public bool AddUser(UserInfo userinfo)
        {
            string strsql = "insert into [User]( Name, Mobile, Phone, QQ, Email, Address, HeadImg, Company) values( @Name, @Mobile, @Phone, @QQ, @Email, @Address, @HeadImg, @Company)";
            SqlParameter [] param = new SqlParameter[]{
                new SqlParameter ("@Name",SqlDbType.NVarChar,50),
                new SqlParameter("@Mobile",SqlDbType.VarChar,50),
                new SqlParameter("@Phone",SqlDbType.VarChar,50),
                new SqlParameter("@QQ",SqlDbType.VarChar,50),
                new SqlParameter("@Email",SqlDbType.VarChar,50),
                new SqlParameter("@Address",SqlDbType.VarChar,200),
                new SqlParameter("@headImg",SqlDbType.VarChar,100),
                new SqlParameter("@Company",SqlDbType.VarChar,100),
                
            };

            param[0].Value = userinfo.Name;
            param[1].Value = userinfo.Mobile;
            param[2].Value = userinfo.Phone;
            param[3].Value = userinfo.QQ;
            param[4].Value = userinfo.Email;
            param[5].Value = userinfo.Address;
            param[6].Value = userinfo.HeadImg;
            param[7].Value = userinfo.Company;

            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, strsql, param) > 0;
        }

        public bool DelUser(int userId)
        {
            string sql = "delete from [User] where [ID]=@ID";
            SqlParameter[] param = new SqlParameter[] { new SqlParameter("@ID", SqlDbType.Int) };
            param[0].Value = userId;
            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, sql, param) > 0;
        }

        public bool EditUser(UserInfo userinfo)
        {
            string strsql = "update [User] set [Mobile]=@Mobile,[Phone]=@Phone,[QQ]=@QQ,[Email]=@Email,[Company]=@Company,[Address]=@Address,[Name]=@Name where [ID]=@ID";
            SqlParameter[] param = new SqlParameter[] {
                new SqlParameter("@Mobile", SqlDbType.VarChar, 50), 
                new SqlParameter("@Phone", SqlDbType.VarChar, 50), 
                new SqlParameter("@QQ", SqlDbType.VarChar, 50), 
                new SqlParameter("@Email", SqlDbType.VarChar, 50), 
                new SqlParameter("@Company", SqlDbType.VarChar, 100), 
                new SqlParameter("@Address", SqlDbType.VarChar, 200), 
                new SqlParameter("@Name",SqlDbType.NVarChar,50),
                new SqlParameter("@ID", SqlDbType.Int) 
            };

            param[0].Value = userinfo.Mobile;
            param[1].Value = userinfo.Phone;
            param[2].Value = userinfo.QQ;
            param[3].Value = userinfo.Email;
            param[4].Value = userinfo.Company;
            param[5].Value = userinfo.Address;
            param[6].Value = userinfo.Name;
            param[7].Value = userinfo.ID;

            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, strsql, param) > 0;
        }

        public bool IsExistUser(string Name)
        {
            string strsql = "select count(*) from [User] where [Name]=@Name";
            SqlParameter[] param = new SqlParameter[] { new SqlParameter("@Name", SqlDbType.NVarChar, 30) };
            param[0].Value = Name;
            return (int)SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.Text, strsql, param) > 0;
        }


        public IList<UserInfo> GetUserList()
        {
            IList<UserInfo> list = new List<UserInfo>();
            string strsql = "SELECT   ID, Name, Mobile, Phone, QQ, Email, Address, HeadImg, Company FROM [User] ORDER BY ID DESC";

            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, strsql, null))
            {
                while (rdr.Read())
                {
                    list.Add(new UserInfo
                    {
                        ID = rdr.GetInt32(0),
                        Name = rdr.GetString(1),
                        Mobile = rdr.GetString(2),
                        Phone = rdr.GetString(3),
                        QQ = rdr.GetString(4),
                        Email = rdr.GetString(5),
                        Address = rdr.GetString(6),
                        HeadImg = rdr.GetString(7),
                        Company = rdr.GetString(8)
                    });
                }
            }
            return list;
        }

        public UserInfo GetUser(int UserId)
        {
             string strsql = "SELECT   ID, Name, Mobile, Phone, QQ, Email, Address, HeadImg, Company FROM [User] where ID=@UserId";
            SqlParameter[] param = new SqlParameter[] { new SqlParameter("@UserId",SqlDbType.Int) };
            param[0].Value = UserId;
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, strsql, param))
            {
                if (rdr.Read())
                {
                    return new UserInfo
                    {
                        ID = rdr.GetInt32(0),
                        Name = rdr.GetString(1),
                        Mobile = rdr.GetString(2),
                        Phone = rdr.GetString(3),
                        QQ = rdr.GetString(4),
                        Email = rdr.GetString(5),
                        Address = rdr.GetString(6),
                        HeadImg = rdr.GetString(7),
                        Company = rdr.GetString(8)
                    };
                }
                else
                {
                    return new UserInfo();
                }
            }
        }

        public UserInfo GetUser(string Name)
        {
            string strsql = "SELECT   ID, Name, Mobile, Phone, QQ, Email, Address, HeadImg, Company FROM [User] where Name=@Name";
            SqlParameter[] param = new SqlParameter[] { new SqlParameter("@Name", SqlDbType.NVarChar) };
            param[0].Value = Name;
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, strsql, param))
            {
                if (rdr.Read())
                {
                    return new UserInfo
                    {
                        ID = rdr.GetInt32(0),
                        Name = rdr.GetString(1),
                        Mobile = rdr.GetString(2),
                        Phone = rdr.GetString(3),
                        QQ = rdr.GetString(4),
                        Email = rdr.GetString(5),
                        Address = rdr.GetString(6),
                        HeadImg = rdr.GetString(7),
                        Company = rdr.GetString(8)
                    };
                }
                else
                {
                    return new UserInfo();
                }
            }
        }

       
    }
}